package exttable.formgenerator;

import dbutils.DbUtils;
import dbutils.StoredQueries;
import exttable.SGTTestParametrs;

import javax.swing.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;


/**
 * Created by IntelliJ IDEA.
 * User: ADMIN
 * Date: 20.04.2010
 * Time: 16:42:38
 * To change this template use File | Settings | File Templates.
 */
public class ViewGenerator extends JFrame {
    PreparedStatement ps;

    public ViewGenerator(String tableName) {
        try {
            Connection conn = DbUtils.getDBConnectionGeneral();
//            PreparedStatement ps = conn.prepareStatement("");
            String query="";
            if (tableName.equals("groups")) {
                ps = conn.prepareStatement(StoredQueries.getQuery(StoredQueries.GROUPS_SELECT));
            } else if (tableName.equals("probationers")) {
                if(SGTTestParametrs.getInstance().getUserAccessLevel() < 4){
                   query = "SELECT\n" +
                            "  probationers.probationer_id,\n" +
                            "  volunteers.volunteer_code,\n" +
                            "  probationers.probationer_code,\n" +
                            "  groups.phase_id,\n" +
                            "  probationers.date_visit_zero,\n" +
                            "  probationers.excluded\n" +
                            "FROM\n" +
                            "  groups\n" +
                            "  INNER JOIN probationers ON (groups.group_id = probationers.group_id)\n" +
                            "  INNER JOIN volunteers ON(volunteers.volunteer_id=probationers.volunteer_id)\n" +
                            "  WHERE probationers.probationer_id IN(" + StaticMethods.getStringForInFromProbationerList(FilterSelector.getProbationerToReturnList()) + ")" +
                            " ORDER BY volunteer_code";
                }else{
                    query = "SELECT\n" +
                            "  probationers.probationer_id,\n" +
                            "  volunteers.volunteer_code,\n" +
                            "  probationers.probationer_code,\n" +
                            "  groups.phase_id,\n" +
                            "  probationers.group_id,\n" +
                            "  groups.group_code,  \n" +
                            "  probationers.date_visit_zero,\n" +
                            "  probationers.excluded\n" +
                            "FROM\n" +
                            "  groups\n" +
                            "  INNER JOIN probationers ON (groups.group_id = probationers.group_id)\n" +
                            "  INNER JOIN volunteers ON(volunteers.volunteer_id=probationers.volunteer_id)\n" +
                            "    WHERE groups.phase_id IN(" + StaticMethods.getStringForInFromString(FilterSelector.getPhaseToReturnList()) + ")\n" +
                            "    AND    \n" +
                            "    probationers.group_id IN(" + StaticMethods.getStringForInFromGroup(FilterSelector.getGroupToReturnList()) + ")\n" +
                            "ORDER BY volunteer_code";
                }


//                ps = conn.prepareStatement(StoredQueries.getQuery(StoredQueries.PROBATIONERS_SELECT));
//                System.out.println(query);
                ps = conn.prepareStatement(query);
            } else if (tableName.equals("volunteers")) {
                ps = conn.prepareStatement(StoredQueries.getQuery(StoredQueries.VOLUNTEERS_SELECT));
                /*
                String query="SELECT\n" +
                        "  volunteers.volunteer_id,\n" +
                        "  volunteers.volunteer_code,\n" +
                        "  volunteers.volunteer_date_birth,\n" +
                        "  dic_nationalities.name_ru AS nationality_id,\n" +
                        "  dic_sex.name_ru AS sex_id,\n" +
                        "  volunteers.volunteer_lastname,\n" +
                        "  volunteers.volunteer_firstname,\n" +
                        "  volunteers.volunteer_patronymic_name,\n" +
                        "  volunteers.volunteer_identity_card_number,\n" +
                        "  volunteers.volunteer_identity_card_date_issue,\n" +
                        "  volunteers.place_of_living,\n" +
                        "  volunteers.place_of_employment,\n" +
                        "  dic_marital_status.name_ru AS marital_status_id,\n" +
                        "  volunteers.phone_number,\n" +
                        "  volunteers.mobile_phone_number,\n" +
                        "  volunteers.email,\n" +
                        "  dic_occupations.name_ru AS occupation_id,\n" +
                        "  volunteers.smoking_now,\n" +
                        "  volunteers.how_long_years_smoking,\n" +
                        "  dic_type_smoking.name_ru AS type_smoking_id,\n" +
                        "  dic_how_many_cigarretes.name_ru AS how_many_cigarretes_id,\n" +
                        "  volunteers.drink_beer,\n" +
                        "  dic_how_much_beer_week.name_ru AS how_much_beer_week_id,\n" +
                        "  volunteers.drink_wine,\n" +
                        "  t1.name_ru AS how_much_wine_week_id,\n" +
                        "  volunteers.drink_other_alcoholic_drinks,\n" +
                        "  t2.name_ru AS how_much_drink_other_alcoholic_drinks_week_id,\n" +
                        "  volunteers.height,\n" +
                        "  volunteers.index_body_weight,\n" +
                        "  volunteers.max_arterial_pressure,\n" +
                        "  volunteers.min_arterial_pressure\n" +
                        "   FROM\n" +
                        "  dic_nationalities\n" +
                        "  INNER JOIN volunteers ON (dic_nationalities.nationality_id = volunteers.nationality_id)\n" +
                        "  INNER JOIN dic_sex ON (volunteers.sex_id = dic_sex.sex_id)\n" +
                        "  INNER JOIN dic_marital_status ON (volunteers.marital_status_id = dic_marital_status.marital_status_id)\n" +
                        "  INNER JOIN dic_occupations ON (volunteers.occupation_id = dic_occupations.occupation_id)\n" +
                        "  INNER JOIN dic_type_smoking ON (volunteers.type_smoking_id = dic_type_smoking.type_smoking_id)\n" +
                        "  INNER JOIN dic_how_many_cigarretes ON (volunteers.how_many_cigarretes_id = dic_how_many_cigarretes.how_many_cigarretes_id)\n" +
                        "  INNER JOIN dic_how_much_beer_week ON (volunteers.how_much_beer_week_id = dic_how_much_beer_week.how_much_beer_week_id)\n" +
                        "  INNER JOIN dic_how_much_wine_week AS t1 ON (volunteers.how_much_wine_week_id = t1.how_much_wine_week_id)\n" +
                        "  INNER JOIN dic_how_much_wine_week AS t2 ON (volunteers.how_much_drink_other_alcoholic_drinks_week_id = t2.how_much_wine_week_id)\n" +
                        "    \n" +
                        "WHERE volunteers.volunteer_id IN(\n" +
                        "SELECT volunteer_id FROM probationers INNER JOIN groups\n" +
                        "ON(probationers.group_id=groups.group_id)\n" +
                        "WHERE groups.phase_id IN("+StaticMethods.getStringForInFromString(FilterSelector.getPhaseToReturnList())+")\n" +
                        "and probationers.group_id IN("+StaticMethods.getStringForInFromGroup(FilterSelector.getGroupToReturnList())+")\n" +
                        ")\n" +
                        "ORDER BY volunteer_code";
                ps=conn.prepareStatement(query);
                */
            }
            ResultSet rs = ps.executeQuery();
            setDefaultCloseOperation(JFrame.DISPOSE_ON_CLOSE);

            // наша модель
            DatabaseTableModel dbm = new DatabaseTableModel(false);
            dbm.setDataSource(rs, tableName, conn);
// таблица и окно
            JTable table = new JTable(dbm);
            //horizontal scroll
            table.setAutoResizeMode(JTable.AUTO_RESIZE_OFF);
            ViewDataBaseTableModel.setColumnSize(table, 8);
            JScrollPane jsp = new JScrollPane(table);
            add(jsp);
            //setSize(400, 300);
            pack();
            setVisible(true);
            conn.close();
        } catch (Exception excep) {
            excep.printStackTrace();
        }
    }

}
